package jdbc1;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC是Java连接数据库的技术
 * 基本链接，最原始的方法1.0
 */
public class Jdbc {
    private String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
    private String user="root";
    private String passWord="123456";
    private String driver;

    public Jdbc() throws Exception {
        Properties properties = new Properties();
        //注意改\\
        FileInputStream fileInputStream = new FileInputStream("src\\resourse.properties");
        properties.load(fileInputStream);
        user = properties.getProperty("user");
        passWord = properties.getProperty("password");
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
    }

    public Jdbc(String url, String user, String passWord) {
        this.url = url;
        this.user = user;
        this.passWord = passWord;
    }

    /**
     * 1、查找数据
     * @throws Exception
     */
    public void findAllDate(String sql) throws Exception {
        Connection conn = getConn();
        //4.获取查询对象（创建一个用于将 SQL 语句发送到数据库的 Statement 对象）
        Statement statement = conn.createStatement();
        System.out.println("statement = " + statement);
        //5.使用查询对象执行sql
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("resultSet = " + resultSet);
        //6.解析数据
        while (resultSet.next()){
            int sno = resultSet.getInt("sno");
            String sname = resultSet.getString("sname");
            String ssex = resultSet.getString("ssex");
            System.out.println(sno+" | "+sname+" | "+ssex);
        }
        //7.释放资源（释放连接对象和查询对象）
        statement.close();
        conn.close();
    }

    /**
     * 2、插入数据
     * @throws Exception
     * @Test 注解，单元测试，无需调用即可测试
     */
    @Test
    public void insertDate(String sql) throws Exception {
        Connection conn = getConn();
        int i = stateObj(conn,sql);
        System.out.println("插入数据：" + i);
    }

    /**
     * 3、删除数据
     * @throws Exception
     */
    @Test
    public void deleteDate(String sql) throws Exception {
        Connection conn = getConn();
        int i = stateObj(conn,sql);
        System.out.println("删除数据：" + i);
    }

    /**
     * 更新数据
     * @throws Exception
     */
    @Test
    public void updateDate(String sql) throws Exception {
        Connection conn = getConn();
        int i = stateObj(conn,sql);
        System.out.println("更新数据：" + i);
    }

    /**
     *
     * @return
     */
    public Connection getConn(){
        Connection conn=null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,passWord);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     *
     * @param conn
     * @param sql
     * @return
     */
    public int stateObj(Connection conn,String sql){
        int i=0;
        try {
            Statement statement = conn.createStatement();
            i = statement.executeUpdate(sql);
            statement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 检验是否管理员
     * @param admin
     * @param passWord
     * @return
     * @throws Exception
     */
    public boolean isAdmin(String admin,String passWord) throws Exception {
        Connection conn = getConn();
        String sql="select * from admins where admin='"+admin+"' and password='"+passWord+"'";
        System.out.println("sql = " + sql);
        Statement statement = conn.createStatement();
        // select * from admins where admin='aaa' and password='12345'
        ResultSet resultSet = statement.executeQuery(sql);
        boolean next = resultSet.next();
        return next;
    }
}
